<?php
/**
 * This file contains the class SubmitPersonDB which provides
 * all of the database requirements for submitting a person
 *
 * @copyright  2009 A Memory Tree
 * @license    A Memory Tree License
 * @version    $Id:$
 */
/**
 * This class provides access to the database for MemberDB
 *
 * @copyright  2009 A Memory Tree
 * @license    A Memory Tree License
 * @version    Release: @package_version@
 */
class SubmitPersonDB
{
    /**
      * Provides access to the database
     *
     * @var Db
     */
    private $_db;
    /**
     * Create a new instance of this class
     * 
     * @return void
     * @param Db $db
     */
    function __construct($db)
    {
        $this->_db = $db;
    }
    /**
         * Escape the provided value
         *
         * @return string
         * @param mixed $value
         */
    function escape($value)
    {
        $db = $this->_db;
        return $db->escape($value);
    }
    /**
     * Get the id number of the named charity
     *
     * @return string
     * @param mixed $charityName
     */
    function getCharityNameID($charityName) {
        $db = $this->_db;
        $charityName = $db->escape($charityName);
        $sql = <<< EOT
            SELECT
                ID
            FROM
                Charity
            WHERE
                Name='$charityName'

EOT;
        $id = '';
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $id = $row['ID'];
        }
        return $db->unescape($id);
    }
    /**
     * Add a charity to the database
     *
     * @return string            The id number of the newly added charity
     * @param mixed $charityName The charity to be added
     */
    function insertCharity($charityName) {
        $db = $this->_db;
        $charityName = $db->escape($charityName);
        $sql = <<< EOT
            INSERT INTO
                Charity(Name)
            VALUES
                ('$charityName')

EOT;
        $db->query($sql);
        return  $db->unescape($db->insertID());
    }
    /**
     * Add a person to the database
     *
     * @return int               The id number of the newly added person
     * @param mixed $surname
     * @param mixed $firstnames
     * @param mixed $formername
     * @param mixed $neename
     * @param mixed $alsoknownas
     * @param mixed $deathdate
     * @param mixed $birthdate
     * @param mixed $age
     * @param mixed $agemeasure
     * @param mixed $cityid
     * @param mixed $formercityid
     * @param mixed $regionid
     * @param mixed $countryid
     * @param mixed $charityid
     * @param mixed $funeralhomeid
     * @param mixed $otherinfo 
     */
    function insertNewPerson($surname, $firstnames, $formername, $neename, $alsoknownas, $deathdate, $birthdate,
            $age, $agemeasure, $cityid, $formercityid, $regionid, $countryid,
            $charityid, $funeralhomeid, $otherinfo) {
        $db = $this->_db;
        $surname = $db->escape($surname);
        $firstnames = $db->escape($firstnames);
        $formername = $db->escape($formername);
        $neename = $db->escape($neename);
        $alsoknownas = $db->escape($alsoknownas);
        $deathdate = $db->escape($deathdate);
        $birthdate = $db->escape($birthdate);
        $age = intval($age);
        $agemeasure = $db->escape($agemeasure);
        $cityid = intval($cityid);
        $formercityid = intval($formercityid);
        $regionid = intval($regionid);
        $countryid = intval($countryid);
        $charityid = intval($charityid);
        $funeralhomeid = intval($funeralhomeid);
        $otherinfo = $db->escape($otherinfo);
        $addedon = gmdate("Y-m-j H:i:s", time() + 3600*12);
        $sql = <<< EOT
            INSERT INTO
                Person(
                    Surname, FirstNames, FormerName, NeeName, AlsoKnownAs, DeathDate, BirthDate, 
                    Age, AgeMeasure, CityTownID, FormerCityTownID, RegionID, CountryID, 
                    CharityID, FuneralHomeID, OtherInfo, AddedOn
                )
                VALUES (
                    '$surname', '$firstnames', '$formername', '$neename', '$alsoknownas', '$deathdate', '$birthdate', 
                    '$age', '$agemeasure', $cityid, $formercityid, $regionid, $countryid,
                    $charityid, $funeralhomeid, '$otherinfo', '$addedon'
                )

EOT;
        $db->query($sql);
        return $db->insertID();
    }
    /**
     * Get the AddListingNotify value for the funeral home
     *
     * @return int
     * @param int $funeralHomeId
     */
    function getAddListingNotify($funeralHomeId)
    {
        $db = $this->_db;
        $addListingNotify = 0;
        $sql = <<< EOT
            SELECT AddListingNotify
            FROM FuneralDetails
            WHERE FuneralHomeID = %d
EOT;
        $sql = sprintf($sql, $funeralHomeId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $value = $row['AddListingNotify'];
            $addListingNotify = intval($value);
        }
        return $addListingNotify;
    }
    /**
     * Get the funeral home email
     *
     * @return string
     * @param int $funeralHomeId
     */
    function getFuneralHomeEmail($funeralHomeId)
    {
        $db = $this->_db;
        $sql = <<< EOT
            SELECT Email
            FROM FuneralDetails
            WHERE FuneralHomeID = %d
EOT;
        $sql = sprintf($sql, $funeralHomeId);
        $result = $db->query($sql);
        $email = '';
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $email = $db->unescape($row['Email']);
        }
        return $email;
    }
    /**
     * Get the funeral home Name
     *
     * @return string
     * @param int $funeralHomeId
     */
    function getFuneralHomeName($funeralHomeId)
    {
        $db = $this->_db;
        $sql = <<< EOT
            SELECT Name
            FROM FuneralHome
                INNER JOIN FuneralDetails
                    ON FuneralHome.ID = FuneralDetails.FuneralHomeID
            WHERE FuneralHomeID = %d
EOT;
        $sql = sprintf($sql, $funeralHomeId);
        $result = $db->query($sql);
        $name = '';
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $name = $db->unescape($row['Name']);
        }
        return $name;
    }
}
?>